<?php
// $Id$

include_once('BaseModel.class.php');

class OutboxManagerModel extends BaseModel
{
    function collect($offset=null,$limit=null,$search=array(),$by='',$sort='',$u_id='',$u_prv='')
	{
                $studno = !empty($search['stud_no']) ? $search['stud_no'] : ''; 
                $studname = !empty($search['name']) ? $search['name'] : ''; 
                $mobileno = !empty($search['msisdn']) ? $search['msisdn'] : ''; 
                $date = !empty($search['transaction_date']) ? $search['transaction_date'] : ''; 
                $status = !empty($search['status']) ? $search['status'] : '';
                $group = !empty($search['memgroup']) ? $search['memgroup'] : '';

                $where = (!empty($status)) ? "WHERE otbx.status='$status'":"";
		$where .= (!empty($studno)) ? "AND  mem.stud_no LIKE '$studno%'" : "";
		$where .= (!empty($studname)) ? "AND (mem.firstname LIKE '$studname%' OR mem.lastname LIKE '$studname%')":"";
		$where .= (!empty($mobileno)) ? "AND otbx.msisdn LIKE '%$mobileno%'":"";
                $where .= (!empty($date)) ? "AND otbx.created_on LIKE '$date%'":"";
                $where .= (!empty($group)) ? "AND grp.id='$group'":"";

		if (is_null($offset)) {
            
                      $query = "SELECT COUNT(*)
                                FROM `outbox` otbx 
                                JOIN messages msgs ON msgs.id = otbx.message_id 
                                LEFT JOIN members mem ON mem.id = otbx.member_id 
                                LEFT JOIN groups grp ON grp.id = otbx.group_id $where AND otbx.active=1 AND msgs.by NOT LIKE 'dtr-system'";
		} else {
                     $query="SELECT otbx.*,grp.name as memgroup,mem.stud_no,
                                    CONCAT(mem.firstname,' ',mem.lastname) as fullname,mem.type,
                                    msgs.message,msgs.`by` as sent_by
                             FROM `outbox` otbx 
                             JOIN messages msgs ON msgs.id = otbx.message_id 
                             LEFT JOIN members mem ON mem.id = otbx.member_id 
                             LEFT JOIN groups grp ON grp.id = otbx.group_id $where AND otbx.active=1 AND msgs.by NOT LIKE 'dtr-system'
                             GROUP BY otbx.id ORDER BY $by $sort LIMIT $offset,$limit";
//var_dump($query);
		}
		return $this->query($query, true);
    }

    function detail ($id)
    {
        $query = "SELECT outb.id,memb.stud_no,CONCAT(memb.lastname,', ',memb.firstname,', ',memb.middlename) as full_name,
                    outb.msisdn,outb.created_on,msgs.message,msgs.`by` as sent_by 
                  FROM `outbox` outb 
                  JOIN messages msgs ON msgs.id = outb.message_id 
                  LEFT JOIN members memb ON memb.`msisdn` = outb.`msisdn` WHERE outb.id='$id' and msgs.`by` NOT LIKE 'dtr-system'";
        return $this->query($query, true);
    }

    /*
     * Add members and return the new member_id
     *
     */
    function ins($detail)
    {
        return $this->insert('outbox', $detail);
        return $this->last_insert_id('outbox');
    }

    /*
     * Update categories for a particular categories_id
     *
     */
    function edit ($id, $detail)
    {
        return $this->update('outbox', $detail, "id = '$id'");
    }

    /*
     * Delete members detail for a particular member_id
     *
     */
    function deactivate ($id)
    {
        $query = "UPDATE outbox SET active = '0' WHERE id='$id'";
        return $this->query($query, false);
    }
    function activate ($id)
    {
        $query = "UPDATE outbox SET active = '1' WHERE id='$id'";
        return $this->query($query, false);
    }
}

?>
